/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package lcfgly.spring.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import lcfgly.database.LIDatabase;
import lcfgly.database.LSqlEmptyException;
import lcfgly.database.LSqlException;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * LJdbcTemple
 * 使用JdbcTemplate实现LIDatabase接口用户数据库操作
 * @author lcfgly
 * @Date 2015-1-29 9:36:52
 * @version 1.0
 */
public class LJdbcTemple implements LIDatabase{

    JdbcTemplate tp;
    
    public LJdbcTemple(JdbcTemplate tp) {
        this.tp=tp;
    }

    @Override
    public void execSql(String sql, Object... args) throws LSqlException {
        try{
            tp.update(sql, args);
        }catch(DataAccessException ex){
            throw new LSqlException(ex);
        }
    }

    @Override
    public int updateSql(String sql, Object... args) throws LSqlException {
        try{
            return tp.update(sql, args);
        }catch(DataAccessException ex){
            throw new LSqlException(ex);
        }
    }

    @Override
    public int deleteSql(String sql, Object... args) throws LSqlException {
        try{
            return tp.update(sql, args);
        }catch(DataAccessException ex){
            throw new LSqlException(ex);
        }
    }

    @Override
    public int insertBackId(final String sql, final Object... args) throws LSqlException {
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            tp.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws java.sql.SQLException {
                    PreparedStatement ps = tp.getDataSource().getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    int i = 1;
                    for (Object object1 : args) {
                        ps.setObject(i++, object1);
                    }
                    return ps;
                }
            }, keyHolder);
            return keyHolder.getKey().intValue();
        } catch (DataAccessException ex) {
            throw new LSqlException(ex);
        }
    }

    @Override
    public boolean insert(String sql, Object... args) throws LSqlException {
        try{
            return tp.update(sql, args)>0;
        }catch(DataAccessException ex){
            throw new LSqlException(ex);
        }
    }

    @Override
    public int count(String sql, Object... args) throws LSqlException {
        try{
            String ss = sql.trim().replaceFirst("^(?i)select .*? from (.*) (order by|group by) (.*)", "SELECT count(1) FROM $1");
            return tp.queryForObject(ss, args, Integer.class);
        }catch(EmptyResultDataAccessException e){
            return 0;
        }catch(DataAccessException ex){
            throw new LSqlException(ex);
        }
    }

    @Override
    public Map<String, Object> one(String sql, Object... args) throws LSqlException {
        try {
             return tp.queryForMap(sql, args);
        } catch (EmptyResultDataAccessException e) {
            throw new LSqlEmptyException();
        } catch (DataAccessException e){
            throw new LSqlException(e);
        }
    }

    @Override
    public List<Map<String, Object>> rows(String sql, Object... args) throws LSqlException {
        try {
             return tp.queryForList(sql, args);
        } catch (EmptyResultDataAccessException e) {
            throw new LSqlEmptyException();
        } catch (DataAccessException e){
            throw new LSqlException(e);
        }
    }

}
